"""
@Time : 2021/3/31 4:45 PM 
@Author : Xiaoming
推荐系统mysql数据库初始化
"""
import numpy as np
import pandas as pd
import pymysql
conn = pymysql.connect(
                host='localhost',
                port=3306,
                user='root',
                password='12345678',
                database='rec_demo',
                cursorclass=pymysql.cursors.DictCursor,
                use_unicode=True,
                charset='utf8')


def create_movie():
    df = pd.read_csv('../RawData/movies.csv')
    df['glist'] = df['genres'].str.split('|')
    df = df.drop(columns=['genres'])
    df['releaseYear'] = df['title'].map(lambda x: x[-5:-1])
    df['title'] = df['title'].map(lambda x: x[:-7])

    df_link = pd.read_csv('../RawData/links.csv')

    df_movie = df.merge(df_link)
    df_movie.replace(np.nan, 0, inplace=True)
    df_movie['tmdbId'] = df_movie['tmdbId'].astype('int')

    data_list = []
    for num, row in df_movie.iterrows():
        data_list.append((row['movieId'], row['title'], row['imdbId'], row['tmdbId'], row['releaseYear'], str(row['movieId']) + '.jpg'))

    with conn.cursor() as update_cur:
        query = '''
            insert into movie(id, title, imdb, tmdb, release_year, cover) values(%s, %s, %s, %s, %s, %s)
            '''
        count = update_cur.executemany(query, data_list)
        print(count)
        conn.commit()


def create_genre():
    df = pd.read_csv('../RawData/movies.csv')
    df['glist'] = df['genres'].str.split('|')
    movie_genres = df['glist'].tolist()
    genre_set = set()
    for genres in movie_genres:
        for genre in genres:
            genre_set.add(genre)
    print(len(genre_set))

    data_list = []
    for genre in genre_set:
        data_list.append(genre)

    with conn.cursor() as update_cur:
        query = '''
                insert into genre(name) values(%s)
                '''
        count = update_cur.executemany(query, data_list)
        print(count)
        conn.commit()


def create_movie_genre():
    df = pd.read_csv('../RawData/movies.csv')
    df['glist'] = df['genres'].str.split('|')
    df = df.drop(columns=['genres'])

    with conn.cursor() as genre_cur:
        query = '''
            select id, name from genre
            '''
        genre_cur.execute(query)
        genres = genre_cur.fetchall()

    genre_name_id_dict = {}
    for item_dict in genres:
        genre_name_id_dict[item_dict['name']] = item_dict['id']

    data_list = []
    for num, row in df.iterrows():
        for genre in row['glist']:
            data_list.append((row['movieId'], genre_name_id_dict.get(genre)))

    with conn.cursor() as insert_cur:
        query = '''
            insert into movie_genre(movie_id, genre_id) values(%s, %s)
            '''
        count = insert_cur.executemany(query, data_list)
        conn.commit()
        print(count)


def create_user_movie_score():
    df = pd.read_csv('../RawData/ratings.csv')
    data_list = []
    for num, row in df.iterrows():
        data_list.append((int(row['userId']), int(row['movieId']), float(row['rating']), int(row['timestamp'])))

    with conn.cursor() as insert_cur:
        query = '''
            insert into user_movie_score(user_id, movie_id, score, score_time) values(%s, %s, %s, %s)
            '''
        count = insert_cur.executemany(query, data_list)
        conn.commit()
        print(count)


def create_user():
    df = pd.read_csv('../RawData/ratings.csv')
    df = df.groupby('userId', as_index=False)['rating'].agg(['max', 'min', 'mean', 'count']).reset_index()
    data_list = []
    for num, row in df.iterrows():
        data_list.append((int(row['userId']), 'user' + str(int(row['userId'])), int(row['count']),
                          float(row['min']), float(row['max']), float(row['mean'])))

    with conn.cursor() as insert_cur:
        query = '''
            insert into user(id, name, movie_count, min_score, max_score, avg_score) values(%s, %s, %s, %s, %s, %s)
            '''
        count = insert_cur.executemany(query, data_list)
        conn.commit()
        print(count)


def update_movie():
    df = pd.read_csv('../RawData/ratings.csv')
    df = df.groupby('movieId', as_index=False)['rating'].agg(['mean', 'count']).reset_index()
    data_list = []
    for num, row in df.iterrows():
        data_list.append((int(row['count']), float(row['mean']), int(row['movieId'])))

    with conn.cursor() as insert_cur:
        query = '''
                update movie set score_count=%s, avg_score=%s where id=%s
                '''
        count = insert_cur.executemany(query, data_list)
        conn.commit()
        print(count)


if __name__ == '__main__':
    # create_movie()
    # create_genre()
    # create_movie_genre()
    # create_user_movie_score()
    # create_user()
    update_movie()
